﻿CREATE PROCEDURE [dbo].[proc_Finance_Profit_Regulate_Getlist]
	(
		@CompanyId VARCHAR(500),
		@Approval int,
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@Stext nvarchar(50),
		@StartIndex int,
		@EndIndex int
	)
AS
BEGIN
	DECLARE @strSql VARCHAR(4000)
	Declare @ApprovalSql varchar(100)=''
	Declare @SummarySql varchar(max)=''
	if (@Approval!=10)
	begin
		Set @ApprovalSql=' And Approval='+CONVERT(varchar(11),@Approval)
	end
	if (@Stext!='')
	begin
		Set @SummarySql=' And Summary like ''%'+@Stext+'%'''
	end
	SET @strSql='
	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY RegulateDate)AS Row,
		Id,
		CompanyId,
		CompanyName,
		Amount,
		Summary,
		RegulateDate,
		FillOutUserId,
		FillOutUserName,
		FillOutDate,
		Approval,
		ApprovalComments,
		ApprovalUserId,
		ApprovalName,
		ApprovalTime
		From Finance_Profit_Regulate 
		Where ('''+@CompanyId+'''=''0'' Or CompanyId in ('+@CompanyId+')) and Deleted=0 and
		(Convert(nchar(10),RegulateDate,120) between '''+@StartDate+''' and '''+@EndDate+''')
		'+@ApprovalSql+@SummarySql+'
	)
	
	Select * From list,(Select Count(Id) AS DataCount,Sum(Amount) AS DataTotal From list
	) AS a Where Row between '+convert(varchar,@StartIndex)+' and '+convert(varchar,@EndIndex)+''

	 EXEC(@strSql)
end
	RETURN
